package com.fsbm.ctech.maxis.ebpsbackend;

import com.checkfree.isolutions.persist.PersistServer;
import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;
import com.checkfree.isolutions.utility.*;
import java.sql.*;


public class AcctDetails{

	private String strAcctId = "";
	public AcctDetails(){
	}
	public AcctDetails(String acctId){		this.strAcctId = acctId;	}
    private static Connection getDbConnection() throws Exception {        Connection conn=null;    	String jdbcDriver = "";   		String jdbcDataSource = "";   		String username = "";   		String password = "";
        try{			Properties appCfg = PropertiesConfig.getApplicationSettings();
            if(appCfg.hasProperty("JDBCDriverName") == false){				throw new IllegalArgumentException("JDBC Driver not specified!");			}else{				jdbcDriver = appCfg.getProperty("JDBCDriverName");			}
            if(appCfg.hasProperty("JDBCDataSourceName") == false){				throw new IllegalArgumentException("JDBC Data Source not specified!");			}else{				jdbcDataSource = appCfg.getProperty("JDBCDataSourceName");			}
			if(appCfg.hasProperty("DataBaseUserid") == false){                throw new IllegalArgumentException("Database userid not specified!");			}else{                username = appCfg.getProperty("DataBaseUserid");			}
            if(appCfg.hasProperty("DataBasePassword") == false){                throw new IllegalArgumentException("Database password not specified!");            }else{                password = appCfg.getProperty("DataBasePassword");			}
			Class.forName(jdbcDriver);			conn = DriverManager.getConnection(jdbcDataSource, username, password);			System.out.println("--- Connection opened ---");		}catch(Exception e) {			throw new Exception("Error getting DB connection: " + e);		}
        return conn;    }// end getDbConnection

    public static void freeDbConnection(Connection conn) throws Exception {        try {			if (conn != null){				conn.close();				System.out.println("--- Connection closed ---");			}		}catch(Exception e) {            throw new Exception("Error closing DB connection : " + e);        }   }// end freeDbConnection
/***************************************************************************/
    public static void insertAcctDetails(String acctId,String acctKey,String custGroup) throws Exception {        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				insertAcctDetails(connection, acctId, acctKey, custGroup);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }    }

    public static void insertAcctDetails(Connection connection, String acctId,String acctKey,String custGroup) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO ACCOUNTDETAILS (ACCTID, ACCTKEY, CUSTGROUP) VALUES (?,?,?)");                pstmt.setString(1,acctId);                pstmt.setString(2,acctKey);                pstmt.setString(3,custGroup);                //pstmt.setString(4,supIndicator);                pstmt.execute();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }    }


//----------------

    public static void insertAcctDetails(String acctId,String acctKey,String custGroup,
    	String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        Connection connection = null;
        try {
			connection = getDbConnection();
            if(connection!=null){
				insertAcctDetails(connection, acctId, acctKey, custGroup, supIndicator, smsSubscription, billDeliverSubscription, acctSubscription);
            }
            freeDbConnection(connection);
        }
        catch(Exception e) {
            throw new Exception("Error logging inserting account details table : " + e);
        }
    }

    public static void insertAcctDetails(Connection connection, String acctId,String acctKey,
    	String custGroup,String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        try {
            if(connection!=null){
                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO ACCOUNTDETAILS (ACCTID, ACCTKEY, CUSTGROUP, SUPINDICATOR, SMSSUBSCRIPTION, EMAILDELIVERYSUBSCRIPTION, ACCTSUBSCRIPTION) VALUES (?,?,?,?,?,?,?)");
                pstmt.setString(1,acctId);
                pstmt.setString(2,acctKey);
                pstmt.setString(3,custGroup);
                pstmt.setString(4,supIndicator);
                pstmt.setString(5,smsSubscription);
                pstmt.setString(6,billDeliverSubscription);
                pstmt.setString(7,acctSubscription);


                pstmt.execute();
				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging inserting account details table : " + e);
        }
    }


/***************************************************************************/
    public static void updateAcctDetails(String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateAcctDetails(connection, acctId, acctKey, custGroup, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);        }    }
    public static void updateAcctDetails(Connection connection, String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ?,CUSTGROUP = ?,SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,custGroup);                pstmt.setString(3,supIndicator);                pstmt.setString(4,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);        }    }

/***************************************************************************/
//poon 20051227 start
    public static void updateAcctDetails(String acctId,String custGroup,String supIndicator) throws Exception {
        Connection connection = null;
        try {
			connection = getDbConnection();
            if(connection!=null){
				updateAcctDetails(connection, acctId, custGroup, supIndicator);
            }
            freeDbConnection(connection);
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }
    public static void updateAcctDetails(Connection connection, String acctId,String custGroup,String supIndicator) throws Exception {
        try {
            if(connection!=null){
                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET CUSTGROUP = ?,SUPINDICATOR = ? WHERE ACCTID = ?");
                pstmt.setString(1,custGroup);
                pstmt.setString(2,supIndicator);
                pstmt.setString(3,acctId);
                pstmt.executeUpdate();
				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }

//poon 20051227 end
/***************************************************************************/
//poon 20051226 start
    public static void updateAcctDetails(String acctId,String custGroup,
    	String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        Connection connection = null;
        try {
			connection = getDbConnection();
            if(connection!=null){
				updateAcctDetails(connection, acctId, custGroup, supIndicator, smsSubscription, billDeliverSubscription, acctSubscription);
            }
            freeDbConnection(connection);
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }
    public static void updateAcctDetails(Connection connection, String acctId,
    	String custGroup,String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        try {
            if(connection!=null){
                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET CUSTGROUP = ?,SUPINDICATOR = ?,SMSSUBSCRIPTION = ?,EMAILDELIVERYSUBSCRIPTION = ?,ACCTSUBSCRIPTION = ? WHERE ACCTID = ?");
                pstmt.setString(1,custGroup);
                pstmt.setString(2,supIndicator);
                pstmt.setString(3,smsSubscription);
                pstmt.setString(4,billDeliverSubscription);
                pstmt.setString(5,acctSubscription);
                pstmt.setString(6,acctId);

                pstmt.executeUpdate();
				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }

//poon 20051226 end
//----------------

    public static void updateAcctDetails(String acctId,String acctKey,String custGroup,
    	String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        Connection connection = null;
        try {
			connection = getDbConnection();
            if(connection!=null){
				updateAcctDetails(connection, acctId, acctKey, custGroup, supIndicator, smsSubscription, billDeliverSubscription, acctSubscription);
            }
            freeDbConnection(connection);
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }
    public static void updateAcctDetails(Connection connection, String acctId,String acctKey,
    	String custGroup,String supIndicator,String smsSubscription,String billDeliverSubscription,String acctSubscription) throws Exception {
        try {
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - acctId:'" + acctId +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - acctKey:'" + acctKey +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - custGroup:'" + custGroup +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - supIndicator:'" + supIndicator +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - smsSubscription:'" + smsSubscription +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - billDeliverSubscription:'" + billDeliverSubscription +"'");
System.out.println("^^^^^^^^^^^^^^^updateAcctDetails - acctSubscription:'" + acctSubscription +"'");

            if(connection!=null){
                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ?,CUSTGROUP = ?,SUPINDICATOR = ?,SMSSUBSCRIPTION = ?,EMAILDELIVERYSUBSCRIPTION = ?,ACCTSUBSCRIPTION = ? WHERE ACCTID = ?");
                pstmt.setString(1,acctKey);
                pstmt.setString(2,custGroup);
                pstmt.setString(3,supIndicator);
                pstmt.setString(4,smsSubscription);
                pstmt.setString(5,billDeliverSubscription);
                pstmt.setString(6,acctSubscription);
                pstmt.setString(7,acctId);

                pstmt.executeUpdate();
				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging updating account details table : " + e);
        }
    }


/***************************************************************************/
//-----------------------------------Setter program--------------------------------------------------//
    public static void updateOldAcctKey(String acctId,String acctKey) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateOldAcctKey(connection, acctId, acctKey);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }    }

    public static void updateOldAcctKey(Connection connection, String acctId,String acctKey) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }    }

    public static void updateCustGroup(String acctId,String custGroup) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateCustGroup(connection, acctId, custGroup);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for cust group: " + e);        }    }

    public static void updateCustGroup(Connection connection, String acctId,String custGroup) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET CUSTGROUP = ? WHERE ACCTID = ?");                pstmt.setString(1,custGroup);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for cust group: " + e);        }    }


    public static void updateEmailAdd(String acctId,String emailAdd) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateEmailAdd(connection, acctId, emailAdd);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }


    public static void updateEmailAdd(Connection connection, String acctId,String emailAdd) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILADD = ? WHERE ACCTID = ?");                pstmt.setString(1,emailAdd);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }

    public static void updateSMSNo(String acctId,String smsNo) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSMSNo(connection, acctId, smsNo);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms no: " + e);        }    }


    public static void updateSMSNo(Connection connection, String acctId,String smsNo) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSNO = ? WHERE ACCTID = ?");                pstmt.setString(1,smsNo);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();
				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms no: " + e);        }    }




    public static void updateAcctSubscription(String acctId,String acctSubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateAcctSubscription(connection, acctId, acctSubscription);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for account subscription: " + e);        }    }



    public static void updateAcctSubscription(Connection connection, String acctId,String acctSubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,acctSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for account subscription: " + e);        }    }


    public static void updateEmailDeliverySubscription(String acctId,String emailDeliverySubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateEmailDeliverySubscription(connection, acctId, emailDeliverySubscription);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email delivery subscription: " + e);        }    }



    public static void updateEmailDeliverySubscription(Connection connection, String acctId,String emailDeliverySubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,emailDeliverySubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email delivery subscription: " + e);        }    }

    public static void updateSMSSubscription(String acctId,String smsSubscription) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSMSSubscription(connection, acctId, smsSubscription);           }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms subscription: " + e);        }    }



    public static void updateSMSSubscription(Connection connection, String acctId,String smsSubscription) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,smsSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms subscription: " + e);        }    }



    public static void updateLastViewedDate(String acctId,String lastViewedDate) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateLastViewedDate(connection, acctId, lastViewedDate);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for last viewed date: " + e);        }    }


    public static void updateLastViewedDate(Connection connection, String acctId,String lastViewedDate) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET LASTVIEWEDDATE = ? WHERE ACCTID = ?");                pstmt.setString(1,lastViewedDate);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for last viewed date: " + e);        }    }



    public static void updateSupIndicator(String acctId,String supIndicator) throws Exception {
        Connection connection = null;
        try {			connection = getDbConnection();
            if(connection!=null){				updateSupIndicator(connection, acctId, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sup indicator: " + e);        }    }



    public static void updateSupIndicator(Connection connection, String acctId,String supIndicator) throws Exception {
        try {
            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,supIndicator);                pstmt.setString(2,acctId);                pstmt.executeUpdate();
				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sup indicator: " + e);        }    }



////-----------------------------------------Getter program---------------------------------------------////
	/**
			To retrieve acctid regardless user input is new/old account no.
	*/
	public static String getAcctId(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";
        try{			if (connection!=null){				//search for status records first				query.append("SELECT ACCTID FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");
				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				pstmt.setString(1,acctNo);				pstmt.setString(2,acctNo);

				rs   = pstmt.executeQuery();
				if(rs.next()){					strNewAccountKey = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}
			}
			System.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){			throw new Exception("" + e);		}finally{			if(rs!=null){				rs=null;			}		}
        if (strNewAccountKey == null){			strNewAccountKey = "";		}		return strNewAccountKey;	}

    public static String getAcctId(String acctNo)throws Exception {		Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";
        try{			connection = getDbConnection();
			if (connection!=null){				strNewAccountKey = getAcctId(connection,acctNo);			}

            System.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");
		}catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strNewAccountKey;    }

    public static String getAcctKey(Connection connection,String acctNo)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{                if (connection!=null){
                    //search for status records first                    query.append("SELECT ACCTKEY FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctNo);                    pstmt.setString(2,acctNo);

                    rs   = pstmt.executeQuery();
					if(rs.next()){						strOldAccountKey = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strOldAccountKey == null){			strOldAccountKey = "";		}        return strOldAccountKey;    }

    public static String getAcctKey(String acctNo)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{            connection = getDbConnection();
            if (connection!=null){				strOldAccountKey = getAcctKey(connection,acctNo);            }            System.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strOldAccountKey;    }

    public static String getOldAccountKey(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT ACCTKEY FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strOldAccountKey = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strOldAccountKey == null){			strOldAccountKey = "";		}
        return strOldAccountKey;    }
    public static String getOldAccountKey(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strOldAccountKey = getOldAccountKey(connection,acctId);                }
            System.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strOldAccountKey;    }

    public static String getCustGroup(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT CUSTGROUP FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strCustGroup = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strCustGroup == null){			strCustGroup = "";		}        return strCustGroup;    }


    public static String getCustGroup(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strCustGroup = getCustGroup(connection, acctId);                }            System.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strCustGroup;    }


    public static String getEmailAdd(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT EMAILADD FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strEmailAdd = rs.getString(1);
					}
                    if (pstmt != null) {                        pstmt.close();                    }                }            System.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strEmailAdd == null){			strEmailAdd = "";		}        return strEmailAdd;    }

    public static String getEmailAdd(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strEmailAdd = getEmailAdd(connection, acctId);                }            System.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strEmailAdd;    }

    public static String getSMSNo(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT SMSNO FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strSMSNo = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strSMSNo == null){			strSMSNo = "";		}
        return strSMSNo;    }

    public static String getSMSNo(String acctId)throws Exception {
        Connection connection = null;
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSMSNo = getSMSNo(connection, acctId);                }
            System.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSMSNo;    }

    public static String getAcctSubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT ACCTSUBSCRIPTION FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();
					if(rs.next()){						strAcctSubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strAcctSubscription == null){			strAcctSubscription = "";		}        return strAcctSubscription;    }

    public static String getAcctSubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strAcctSubscription = getAcctSubscription(connection, acctId);                }
            System.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strAcctSubscription;    }

    public static String getEmaiDeliverySubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT EMAILDELIVERYSUBSCRIPTION FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();
					if(rs.next()){						strEmailDeliverySubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strEmailDeliverySubscription == null){			strEmailDeliverySubscription = "";		}
        return strEmailDeliverySubscription;    }

    public static String getEmaiDeliverySubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strEmailDeliverySubscription = getEmaiDeliverySubscription(connection, acctId);                }
            System.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);
        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strEmailDeliverySubscription;    }

    public static String getSMSSubscription(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT SMSSUBSCRIPTION FROM ACCOUNTDETAILS WHERE ACCTID = ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);
                    rs   = pstmt.executeQuery();					if(rs.next()){						strSMSSubscription = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                   }                }            System.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strSMSSubscription == null){			strSMSSubscription = "";		}        return strSMSSubscription;    }

    public static String getSMSSubscription(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSMSSubscription = getSMSSubscription(connection, acctId);                }
            System.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSMSSubscription;    }


    public static String getSupIndicator(Connection connection,String acctId)throws Exception {
        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator= "";
        try{
                if (connection!=null){
                    //search for status records first                    query.append("SELECT SUPINDICATOR FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY= ?");
                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);

                    rs   = pstmt.executeQuery();
					if(rs.next()){						strSupIndicator = rs.getString(1);					}
                    if (pstmt != null) {                        pstmt.close();                    }                }
            System.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }
        if (strSupIndicator == null){			strSupIndicator = "";		}
        return strSupIndicator;    }

    public static String getSupIndicator(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator = "";
        try{            connection = getDbConnection();
                if (connection!=null){					strSupIndicator = getSupIndicator(connection, acctId);                }
            System.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }        return strSupIndicator;    }

    public static boolean checkIfExistInAcctDetails(String acctId)throws Exception {
        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;
        try{
            connection = getDbConnection();
            if (connection!=null){				boolResult = checkIfExistInAcctDetails(connection, acctId);            }            System.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){                freeDbConnection(connection);            }
            if(rs!=null){                rs=null;            }        }        return boolResult;    }
    public static boolean isOldAccountKey(String acctNo)throws Exception {
		boolean result = false;

		try{
			String strOldAccountKey = getAcctKey(acctNo);

			return (strOldAccountKey.equals("") || !strOldAccountKey.equals(acctNo)) ? false : true;
		}catch(Exception e){
			throw new Exception("" + e);
		}
	}

    public static boolean checkIfExistInAcctDetails(Connection connection, String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;
        try{
            if (connection!=null){
				//search for status records first				query.append("SELECT COUNT(ACCTID) FROM ACCOUNTDETAILS WHERE ACCTID= ?");                // prepare the statement and bind parameter                pstmt = connection.prepareStatement(query.toString());                pstmt.setString(1,acctId);
                rs   = pstmt.executeQuery();
				if(rs.next()){					intResult = rs.getInt(1);				}
				if(intResult!=0){					boolResult = true;				}
                if (pstmt != null) {                    pstmt.close();                }            }            System.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{
            if(rs!=null){                rs=null;            }        }        return boolResult;    }}








